﻿/***********************************************
【名称】 蒂森的NVH数据转换器

【更新历史】
2021/12/19 添加了新视图
2021/11/15 发现同一个ResID会有一百多个数据，待进一步分析理解。
2021/11/13 根据新结构进行优化。
2021/10/01 初始化此类。

数据库类型：SQL Server 2012
IP地址：10.0.21.102
mstsc账号：Administrator, Ts#00102
数据库：TS_DCT727_ProdDb
数据位置：dbo.Act_Test_Data

【QDAS视图】
--  V1.0 本人自己编写
SELECT   dbo.ProtocolMain.PkIdent AS ResID, dbo.TypeId.Name AS TestType, dbo.BenchId.Name AS TestBench, 
                dbo.ModeId.Text AS ModeId, dbo.InstrumentId.Text AS Instrument, dbo.ProtocolMain.FkChannel AS Channel, 
                dbo.InstanceId.Text AS Signal, dbo.LocationId.Text AS LocationName, dbo.InstrParamId.Text AS Measurement, 
                dbo.ProtocolMain.Serial AS ProductSerial, dbo.ProtocolMain.Time AS ResTime, 
                dbo.ProtocolMain.TimeOfNextMeasurement AS NextTime, dbo.ProtocolMain.Value, dbo.UnitId.Text AS UnitName, 
                dbo.ProtocolMain.ULimit, dbo.ProtocolMain.LLimit, dbo.ProtocolMain.Position, dbo.ProtocolMain.Result
FROM      dbo.TypeId INNER JOIN
                dbo.ProtocolMain INNER JOIN
                dbo.UnitId ON dbo.ProtocolMain.FkUnit = dbo.UnitId.Id ON dbo.TypeId.Id = dbo.ProtocolMain.Type INNER JOIN
                dbo.ModeId ON dbo.ProtocolMain.FkMode = dbo.ModeId.Id INNER JOIN
                dbo.InstrumentId ON dbo.ProtocolMain.FkInstrument = dbo.InstrumentId.Id INNER JOIN
                dbo.BenchId ON dbo.ProtocolMain.TestBench = dbo.BenchId.Id INNER JOIN
                dbo.LocationId ON dbo.ProtocolMain.FkLocation = dbo.LocationId.Id INNER JOIN
                dbo.InstrParamId ON dbo.ProtocolMain.FkParameter = dbo.InstrParamId.Id INNER JOIN
                dbo.InstanceId ON dbo.ProtocolMain.Ordinal = dbo.InstanceId.Id
WHERE   (dbo.InstrumentId.Text = N'OrderValue')


-- 2.0 由王勇的需求文档初步编写 V1.1
SELECT   dbo.ProtocolMain.PkIdent AS ResID, dbo.TypeId.Name AS TestType_K1001, dbo.BenchId.Name AS TestBench, 
                dbo.ProtocolMain.FkChannel AS Channel, dbo.InstanceId.Text AS Signal, dbo.InstrParamId.Text AS Measurement, 
                dbo.ProtocolMain.Serial AS ProductSerial, dbo.ProtocolMain.Time AS ResTime, 
                dbo.ProtocolMain.TimeOfNextMeasurement AS NextTime, dbo.ProtocolMain.Value, dbo.UnitId.Text AS UnitName, 
                dbo.ProtocolMain.ULimit, dbo.ProtocolMain.LLimit, dbo.ProtocolMain.Position, dbo.ProtocolMain.Result, 
                dbo.Quinters.Id AS K2001, dbo.Quinters.Mode AS K2001_1, dbo.Quinters.Channel AS K2001_2, 
                dbo.Quinters.Location AS K2001_3, dbo.Quinters.Instrument AS K2001_4, dbo.Quinters.Parameter AS K2001_5
FROM      dbo.TypeId INNER JOIN
                dbo.ProtocolMain INNER JOIN
                dbo.UnitId ON dbo.ProtocolMain.FkUnit = dbo.UnitId.Id ON dbo.TypeId.Id = dbo.ProtocolMain.Type INNER JOIN
                dbo.BenchId ON dbo.ProtocolMain.TestBench = dbo.BenchId.Id INNER JOIN
                dbo.InstrParamId ON dbo.ProtocolMain.FkParameter = dbo.InstrParamId.Id INNER JOIN
                dbo.InstanceId ON dbo.ProtocolMain.Ordinal = dbo.InstanceId.Id INNER JOIN
                dbo.Quinters ON dbo.TypeId.Id = dbo.Quinters.Id


-- 查询

SELECT * FROM [TS_DCT727_ProdDb].[dbo].[QdasView] WHERE ResID = 13967 and  (LocationName = 'PumpIn' or LocationName = 'Input')

************************************************/
using QDAS;
using QDasConverter.Utils;
using System;
using System.Collections.Generic;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using WindGoes6.Database;

namespace QDasConverter.Core
{
    /// <summary>
    /// 蒂森的NVH数据转换器。
    /// </summary>
    public class C2021T04_DisenNVH : ConvertBase
    {
        public C2021T04_DisenNVH()
        {
            DisplayName = "蒂森 NVH DF727A03转换器";
            TableName = "ProtocolMain";
            TableIDName = "PkIdent";
            Version = "alpha1 Released Date: 2021/10/01 Inner Name: C2021_DisenNVH";
            Version = "alpha1 Released Date: 2021/11/13 Inner Name: C2021_DisenNVH";
        }


        public override bool OnStart()
        {
            if (ConvertBase.DebugMode)
            {
                SQLManager.CommonConnectionString = "Data Source = 192.168.2.14,1433;Initial Catalog = QS_727_DB;User ID = sa; Password = root00;";
            }

            return true;
        }

        public override void OneLoop_Process()
        {
            BatchCount = 5;
            // ResID	TestType	TestBench	ModeId	Instrument	Channel	Signal	LocationName	Measurement	ProductSerial	ResTime	NextTime	Value	UnitName	ULimit	LLimit	Position	Result
            // 294810  DF727A34 TS2 DCT727  1 - C OrderValue  8   FixFs,InputSpd PumpIn  Pump_H1 S40135C15000100AW02AAMA13257    2021 - 05 - 04 13:12:26.000 NULL    78.0568 dB(g)   100 81.7423 67  4
            SqlCommand = $"SELECT * FROM [TS_DCT727_ProdDb].[dbo].[QdasView] WHERE ResID = {LastResID} and  (LocationName = 'PumpIn' or LocationName = 'Input')";
            sm.CommandText = "SELECT * FROM [TS_DCT727_ProdDb].[dbo].[QdasView]";
            sm.CommandText += $"WHERE ResID >= {LastResID} and ResID < {LastResID + BatchCount}";
            sm.CommandText += "and  (LocationName = 'PumpIn' or LocationName = 'Input')";

            // 2022/01/12 按新需求文档更新
            // 注意：由于同一ID的测量值特别多，所以每次只取10个ID的所有值，并不使用 TOP 语法。
            sm.CommandText = $@"
SELECT 
              dbo.ProtocolMain.PkIdent AS ResID, dbo.TypeId.Name AS TestType, dbo.BenchId.Name AS TestBench, 
                dbo.ModeId.Text AS ModeId, dbo.InstrumentId.Text AS Instrument, dbo.ProtocolMain.FkChannel AS Channel, 
                dbo.InstanceId.Text AS Signal, dbo.LocationId.Text AS LocationName, dbo.InstrParamId.Text AS Measurement, 
                dbo.ProtocolMain.Serial AS ProductSerial, dbo.ProtocolMain.Time AS ResTime, 
                dbo.ProtocolMain.TimeOfNextMeasurement AS NextTime, dbo.ProtocolMain.Value, dbo.UnitId.Text AS UnitName, 
                dbo.ProtocolMain.ULimit, dbo.ProtocolMain.LLimit, dbo.ProtocolMain.Position, dbo.ProtocolMain.Result
FROM      dbo.TypeId INNER JOIN
                dbo.ProtocolMain INNER JOIN
                dbo.UnitId ON dbo.ProtocolMain.FkUnit = dbo.UnitId.Id ON dbo.TypeId.Id = dbo.ProtocolMain.Type INNER JOIN
                dbo.ModeId ON dbo.ProtocolMain.FkMode = dbo.ModeId.Id INNER JOIN
                dbo.InstrumentId ON dbo.ProtocolMain.FkInstrument = dbo.InstrumentId.Id INNER JOIN
                dbo.BenchId ON dbo.ProtocolMain.TestBench = dbo.BenchId.Id INNER JOIN
                dbo.LocationId ON dbo.ProtocolMain.FkLocation = dbo.LocationId.Id INNER JOIN
                dbo.InstrParamId ON dbo.ProtocolMain.FkParameter = dbo.InstrParamId.Id INNER JOIN
                dbo.InstanceId ON dbo.ProtocolMain.Ordinal = dbo.InstanceId.Id
WHERE   (dbo.InstrumentId.Text = N'OrderValue') and dbo.ProtocolMain.PkIdent >= {LastResID} and dbo.ProtocolMain.PkIdent < {LastResID + BatchCount}"; // and  (LocationName = 'PumpIn' or LocationName = 'Input')";

            var data = sm.GetStrings();
            Common.AddLog($"data.Length={data.Length}, ResID={LastResID}.", LogType.Debug);
            



            //00 ResID                          3
            //01 TestType                     C202
            //02 TestBench                  TS1 DCT727
            //03 ModeId                     1 - C
            //04 Instrument                 OrderValue
            //05 Channel                     8
            //06 Signal                        FixFs, InputSpd
            //07 LocationName           PumpIn
            //08 Measurement            Pump_H1
            //09 ProductSerial             DF727A01_C11004201612
            //10 ResTime                    38:36.0
            //11 NextTime                  42:13.0
            //12 Value                        77.4381
            //13 UnitName                 dB(g)
            //14 ULimit                       120
            //15 LLimit                        0
            //16 Position                     67
            //17 Result                        4


            Dictionary<string, QFile> qfiles = new Dictionary<string, QFile>();

            var ids = data.Select(d => d[0]).Distinct().OrderBy(d => d).ToList();

            string[] station_num = { "0", "1107020", "1107021", "0", "1107022" };
           

            foreach (var row in data)
            {
                // 如果不存在 QFile则进行添加。
                if (!qfiles.ContainsKey(row[01]))
                {
                    var newqfile = new QFile();
                    newqfile[1001] = K1001_Convert(row[01]);
                    newqfile[1002] = K1001_Convert(row[01]);
                    newqfile[1203] = "NVH";
                    newqfile[1206] = "蒂森NVH";
                    qfiles.Add(row[01], newqfile);
                }

                var qf = qfiles[row[01]];
                var k2001 = row[03] + "_" + row[07] + "_" +  row[08];
                if (qf.Charactericstics.Count(ch => ch[2001].ToString() == k2001) == 0)
                {
                    var newch = qf.AddQCharacteristic();
                    newch[2001] = row[03] + "_" + row[07] + "_" + row[08];
                    newch[2002] = row[03] + "_" + row[07] + "_" + row[08];
                    newch[2110] = row[15];
                    newch[2111] = row[14];
                    newch[2142] = row[13];
                }
                var qc = qf.Charactericstics.First(ch => ch[2001].ToString() == k2001);

                var item = qc.AddItem();
                item[0001] = row[12];
                item[0004] = row[10];
                item[0010] = station_num[int.Parse(row[17])];
                item[0014] = row[09];

            }

            foreach (var qf in qfiles.Values)
            {
                SaveToDFQ(qf, OutputDirectory, $"C04_TS_NVH_{qf["1001"]}_{NowString()}.dfq");
            }

        }

        public string K1001_Convert(string value)
        {
            var index = value.IndexOf("llo");
            string[] getAry = value.Split(' ');
            if(getAry.Length == 2)
                return getAry[1]+"("+getAry[0]+")";
            return value;
        }
    }
}
